package com.sample.javabean;
/**
 * 家庭成员信息类
 * @author 深深
 * @version 1.0
 * 
 */
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.sample.action.form.FamilyMessageForm;
import com.sample.jdbc.JdbcUtils;
import com.sample.util.Pager;

public class FamilyMessage {
	private  String family_id;//家庭编号
	private  String cardNo;//身份证号
	private  String family_name;//家庭成员姓名
	private  String persNumber;//参合证号
	private  String address;//家庭地址
	private  String tel;//联系电话
	private  String invNumber;//参合发票号
	private  String sex;//性别
	private  String relationship;//与户主关系
	private  String joinYear;//参合时间
	private  int id;
	private JdbcUtils jdbcUtils;
	private static final Log LOG=LogFactory.getLog(FamilyMessage.class);
	
	public FamilyMessage() {
		jdbcUtils = new JdbcUtils();
	}
	
	/**
	 * 加载成员信息
	 * @throws Exception
	 */
	public void load()throws Exception{
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs =null; 
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			//构造SQL语句
			String sql = "select * from  t_family_message where id='"+this.id+"'";
			// 执行SQL并返回结果
			rs=stmt.executeQuery(sql);
			if(rs.next()){
				this.id =rs.getInt("id");
				this.family_id =rs.getString("family_id");
				this.cardNo =rs.getString("cardNo");
				this.family_name =rs.getString("family_name");
				this.persNumber =rs.getString("persNumber");
				this.address =rs.getString("address");
				this.tel =rs.getString("tel");
				this.invNumber =rs.getString("invNumber");
				this.sex=rs.getString("sex");
				this.relationship=rs.getString("relationship");
				this.joinYear=rs.getString("joinYear");
			}
			//关闭数据库
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException("加载家庭成员信息失败");
		} finally {
			jdbcUtils.close(conn, stmt);
		}	
	}
	/**
	 * 添加家庭成员信息
	 */
	public void add(){
		Connection conn =null;
		Statement stmt=null;
		try{
				
			 conn =jdbcUtils.getConn();
				// 构造SQL语句
			String sql="insert into t_family_message(family_id,cardNo,family_name,persNumber,address,tel,invNumber,sex,relationship,joinYear) values('"+this.family_id+"','"+this.cardNo+"','"+this.family_name+"','"+this.persNumber+"','"+this.address+"','"+this.tel+"','"+this.invNumber+"','"+this.sex+"','"+this.relationship+"','"+this.joinYear+"')";
			
			stmt =conn.createStatement();
			stmt.executeUpdate(sql);
		}catch (Exception e) {
			e.printStackTrace();
			
		}finally{
			jdbcUtils.close(conn, stmt);
			
		}
		
	}
	/**
	 * 删除家庭成员信息
	 */
	public void delFamilyMessage() {

		Connection conn = null;
		Statement stmt = null;
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			//构造sql
			String sql = "delete from t_family_message  where id='"+this.id+"'";
			stmt.executeUpdate(sql);
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt);
		}

	}
	
	/**
	 * 修改家庭成员信息
	 */
	public void editFamilyMessage() {

		Connection conn = null;
		Statement stmt = null;
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			//构造SQL
			String sql ="update  t_family_message set id='"+this.id+"',family_id='"+this.family_id+"',cardNo='"+this.cardNo+"',family_name='"+this.family_name+"',persNumber='"+this.persNumber+"',address='"+this.address+"',tel='"+this.tel+"',invNumber='"+this.invNumber+"',sex='"+this.sex+"',relationship='"+this.relationship+"',joinYear='"+this.joinYear+"' where id='"+this.id+"'";
			// 执行SQL并返回结果
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt);
		}

	}
	/**
	 * 分页显示家庭成员信息
	 * @param page
	 * @param size
	 * @param form
	 * @return
	 */
	public Pager<FamilyMessage> list(int page,int size,FamilyMessageForm form) {

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs =null;
		Pager<FamilyMessage> results=new Pager<FamilyMessage>();
		results.setPage(page);
		results.setSize(size);
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			int start=(page-1)*size;
			int limit=size;
			//查询SQL
			String selectSql="select * from t_family_message where 1=1";
			//模糊查询家庭成员姓名
			if(form!=null&&form.getFamily_name()!=null&&!"".equals(form.getFamily_name())){
				selectSql +=" and family_name like'%"+form.getFamily_name()+"%'";
			}
			//统计SQL
			String countSql ="select count(*)as c from ("+selectSql+") as t1";
			//分页SQL
			 String pageSql=selectSql+" limit "+start+","+limit;
			 rs= stmt.executeQuery(countSql);
			 int total=0;
			 while(rs.next()){
				 total=rs.getInt("c");
			 }
			 results.setTotal(total);
			 //查询分页信息
			 rs= stmt.executeQuery(pageSql);
			 while(rs.next()){
					
				  String family_id=rs.getString("family_id");
				  String cardNo=rs.getString("cardNo");
				  String family_name=rs.getString("family_name");
				  String persNumber=rs.getString("persNumber");
				  String address=rs.getString("address");
				  String tel=rs.getString("tel");
				  String invNumber=rs.getString("invNumber");
				  String sex=rs.getString("sex");
				  String relationship=rs.getString("relationship");
				  String joinYear=rs.getString("joinYear");
				  int id=rs.getInt("id");
				  
				  
				
				  FamilyMessage familymeassage=new FamilyMessage();
				  familymeassage.setFamily_id(family_id);
				  familymeassage.setCardNo(cardNo);
				  familymeassage.setFamily_name(family_name);
				  familymeassage.setPersNumber(persNumber);
				  familymeassage.setAddress(address);
				  familymeassage.setTel(tel);
				  familymeassage.setInvNumber(invNumber);
				  familymeassage.setSex(sex);
				  familymeassage.setRelationship(relationship);
				  familymeassage.setJoinYear(joinYear);
				  familymeassage.setId(id);
				  results.getDataSets().add(familymeassage);
			 }
			 results.init();
			
		
		} catch (SQLException e) {
			e.printStackTrace();
			 LOG.info("分页显示家庭成员信息 ");
		} finally {
			jdbcUtils.close(conn, stmt);
		}
		return results;

	}
	// get/set方法
	 
	public String getFamily_id() {
		return family_id;
	}
	public void setFamily_id(String family_id) {
		this.family_id = family_id;
	}
	public String getCardNo() {
		return cardNo;
	}
	public void setCardNo(String cardNo) {
		this.cardNo = cardNo;
	}
	public String getFamily_name() {
		return family_name;
	}
	public void setFamily_name(String family_name) {
		this.family_name = family_name;
	}
	public String getPersNumber() {
		return persNumber;
	}
	public void setPersNumber(String persNumber) {
		this.persNumber = persNumber;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public String getTel() {
		return tel;
	}
	public void setTel(String tel) {
		this.tel = tel;
	}
	public String getInvNumber() {
		return invNumber;
	}
	public void setInvNumber(String invNumber) {
		this.invNumber = invNumber;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public String getRelationship() {
		return relationship;
	}
	public void setRelationship(String relationship) {
		this.relationship = relationship;
	}
	public String getJoinYear() {
		return joinYear;
	}
	public void setJoinYear(String joinYear) {
		this.joinYear = joinYear;
	}
	
	//构造方法
	public FamilyMessage(String family_id, String cardNo, String family_name,
			String persNumber, String address, String tel, String invNumber,
			String sex, String relationship, String joinYear, int id) {
		super();
		this.family_id = family_id;
		this.cardNo = cardNo;
		this.family_name = family_name;
		this.persNumber = persNumber;
		this.address = address;
		this.tel = tel;
		this.invNumber = invNumber;
		this.sex = sex;
		this.relationship = relationship;
		this.joinYear = joinYear;
		this.id = id;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	
	
}
